use master
go
create database Student
use Student
create table stuInfo
(
	stuID int primary key identity(1,1),
	stuName nvarchar(10) not null,
	stuAge nvarchar(30) not null,
	stuSex nvarchar(2) not null,
	[time] datetime
)
insert into stuInfo values('Tom',19,'男',null),('Jack',20,'女',null),
('Rose',21,'男',null),('LUlu',19,'男',null),('Lili',21,'女',null),('abc',20,'男','2007-01-07 01:11:36.590')

create table Course
(
	courseID int primary key identity(1,1),
	courseName nvarchar(10) not null,
	courseMarks int not null
)

insert into Course values('JavaBase',4),('HTML',2),('JavaScript',2),('SqlBase',2)


create table Score
(
	scoreID int primary key identity(1,1),
	stuID int references stuInfo(stuID) not null,
	courseID int references Course(courseID) not null,
	score int not null
)

insert into Score values(1,1,80),(1,2,85),(1,4,50),
(2,1,75),(2,3,45),(2,4,75),
(3,1,45),(4,1,95),(4,2,75),(4,3,90),(4,4,45)

select * from stuInfo
select * from Course
select * from Score

--1.查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分
	select st.stuID ,stuName,COUNT(courseID) 课程数量, AVG(score) 考试平均分 from stuInfo st 
	inner join Score s on st.stuID=s.stuID 
	group by st.stuID,stuName
--2.查询出每门课程的选修的学生的个数和学生考试的总分
	select st.stuID ,stuName,COUNT(courseID) 课程数量, sum(score) 考试总分 from stuInfo st 
	inner join Score s on st.stuID=s.stuID 
	group by st.stuID,stuName
--3.查询出性别一样并且年龄一样的学生的信息
	select s1.* from stuInfo s1
	inner join stuInfo s2
	on (s1.stuSex=s2.stuSex) and (s1.stuAge=s2.stuSex)
--4.查询出学分一样的课程信息
	select distinct c1.* from Course c1
	inner join Course c2
	on (c1.courseID<>c2.courseID) and (c1.courseMarks=c2.courseMarks)
--5.查询出参加了考试的学生的学号，姓名，课程号和分数
	select st.stuID 学号,st.stuName 姓名, sc.courseID 课程号,sc. score 分数 from stuInfo st 
	inner join Score sc
	on st.stuID=sc.stuID
--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数
	select sc.stuID,sc.courseID,courseName,courseMarks, score from Score sc  
	inner join Course c
	on sc.courseID=c.courseID
--7.查询出没有参加考试的学生的学号和姓名
	select st.stuID,stuName from stuInfo st
	left join Score sc
	on st.stuID=sc.stuID
	where sc.scoreID is null
--8.查询出是周六周天来报到的学生
	select * from stuInfo where DATENAME(DW,time)='星期六'or  DATENAME(DW,time)='星期日'
--9.查询出姓名中有字母a的学生的信息
	select * from stuInfo where stuName like '%a%'
--10.查询出选修了2门课程以上的并且考试平均分在70以上的 学生的学号和考试平均分以及选修课程的数量
	select stuID '学号', avg(score) 平均分,COUNT(*) 选课数量 from score
	group by stuID 
	having COUNT(*)>2 and avg(score)>70